import glob
import ntpath
import os
import pandas as pd
from MyConfig import MyConfig
from OraWorker import OraWorker

cur_path = os.path.dirname(os.path.realpath(__file__))
settings = MyConfig(f"{cur_path}\\config.json")
user = settings.get("OracleConfig",defaultValue=None)["user"]
passw = settings.get("OracleConfig",defaultValue=None)["pass"]
dsn = settings.get("OracleConfig",defaultValue=None)["dsn"]
cl = settings.get("OracleConfig",defaultValue=None)["client"]
ora = OraWorker(user,passw,dsn,cl)

def vm_handler(row:pd.Series)->pd.Series:
    try:
        row["Вид материала в SAP ERP"] = str(row["Вид материала в SAP ERP"])[0:4]
        check = ora.product_check("-",str(row["ID в SAP ERP VMZ"]))
        row["Укрупнено в продукт УП"] = check[0]
        print(f"check: {check[0]}")
    except Exception as exp:
        row["Полное наименование материала"]=f"{exp}"
        row["Укрупнено в продукт УП"] = "-"
    finally:
        return row

if __name__=="__main__":
    try:

        pattern="D:\\work\\Укрупнение\\current\\data\\2024*\\*.xlsx"
        dump_dir="D:\\work\\Укрупнение\\current\\debug_data\\"
        exclude_pattern="нение_"
        neuro_columns=["ID в SAP ERP VMZ", "Вид материала в SAP ERP","Полное наименование материала"]
        files=[f for f in glob.glob(pattern) if exclude_pattern not in f] if exclude_pattern is not None else [f for f in glob.glob(pattern)]
        data={}
        list_data=[]
        for f in files:
            short_name = ntpath.basename(f)
            print(f"{f} -> {short_name} ---------------")
            xl = pd.ExcelFile(f)
            for sheet_name in xl.sheet_names:
                df=xl.parse(sheet_name=sheet_name)
                key = f"{dump_dir}[{short_name.replace('.xlsx','')}][{sheet_name}].xlsx"
                if not df.empty:
                    if key not in data.keys():
                        df=df[neuro_columns].apply(vm_handler,axis=1)
                        list_data.append(df) 
        result = pd.concat(list_data)
        result = result.drop_duplicates()
        result.to_excel(f"{dump_dir}neuro_dump.xlsx",index=False)
        print(f"{result.shape} -> done")
        ora.close()   
    except Exception as exp:
        print(f"error: {exp}")